﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;



namespace Rhapsody2Spotify.Lib
{
    /// <summary>
    /// A class for talking to a Rhapsody SQLite database
    /// </summary>
    public class RhapsodyDB
    {
        private string _cs;

        /// <summary>
        /// Returns a new RhapsodyDB instance
        /// </summary>
        /// <param name="connectionString"></param>
        public RhapsodyDB(string connectionString)
        {
            _cs = connectionString;
        }

        /// <summary>
        /// Gets the number of tracks in the library
        /// </summary>
        /// <returns></returns>
        public int GetLibraryTrackCount()
        {
            using (var con = new SQLiteConnection(_cs))
            {
                var com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT COUNT(TRACK_NAME) FROM TRACK WHERE In_Library = 1";

                con.Open();

                return int.Parse(com.ExecuteScalar().ToString());
            }

        }

        /// <summary>
        /// Gets the number of tracks across all playlists
        /// </summary>
        /// <returns></returns>
        public int GetPlaylistTrackCount()
        {
            using (var con = new SQLiteConnection(_cs))
            {
                var com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT COUNT(TRACK_NAME) FROM TRACK  INNER JOIN Playlist_Track ON Playlist_Track.Track_ID = Track.Track_ID";

                con.Open();

                return int.Parse(com.ExecuteScalar().ToString());
            }

        }

        /// <summary>
        /// Gets a list of all playlists, including track information
        /// </summary>
        /// <returns></returns>
        public List<RPlaylist> GetAllPlaylists()
        {

            var Playlists = new List<RPlaylist>();

            using (var con = new SQLiteConnection(_cs))
            {
                var com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT Playlist_ID, Playlist_NAME FROM Playlist";

                con.Open();

                SQLiteDataReader sdr = com.ExecuteReader();

                while (sdr.Read())
                {
                    string _i = sdr["Playlist_ID"].ToString();
                    string _n = sdr["Playlist_Name"].ToString();
                    List<RhapsodyTrack> _t = GetTracksFromPlaylist(_i);
                    Playlists.Add(new RPlaylist(_i, _n, _t));
                    Console.WriteLine(string.Format("{0},{1}", _i, _n));
                }
            }
            return Playlists;
        }

        /// <summary>
        /// Gets all tracks for a particular playlist
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<RhapsodyTrack> GetTracksFromPlaylist(string id)
        {
            var Tracks = new List<RhapsodyTrack>();

            using (var con = new SQLiteConnection(_cs))
            {
                var com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT ARTIST_NAME, TRACK_NAME FROM TRACK  INNER JOIN Playlist_Track ON Playlist_Track.Track_ID = Track.Track_ID AND Playlist_ID = @id";
                com.Parameters.AddWithValue("@id", id);
                con.Open();

                SQLiteDataReader sdr = com.ExecuteReader();

                while (sdr.Read())
                {
                    string _a = sdr["ARTIST_NAME"].ToString();
                    string _t = sdr["TRACK_NAME"].ToString();

                    Tracks.Add(new RhapsodyTrack(_t, _a));
                }
            }
            return Tracks;
        }
    
        /// <summary>
        /// Gets all tracks in the library
        /// </summary>
        /// <returns></returns>
        public List<RhapsodyTrack> GetTracksFromLibrary()
        {
            var Tracks = new List<RhapsodyTrack>();

            using (var con = new SQLiteConnection(_cs))
            {
                var com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT ARTIST_NAME, TRACK_NAME FROM TRACK WHERE In_Library = 1 ";
                con.Open();

                SQLiteDataReader sdr = com.ExecuteReader();

                while (sdr.Read())
                {
                    string _a = sdr["ARTIST_NAME"].ToString();
                    string _t = sdr["TRACK_NAME"].ToString();

                    Tracks.Add(new RhapsodyTrack(_t, _a));
                }
            }
            return Tracks;
        }

    }
}
